﻿using System;
using System.Data.SqlServerCe;
using System.Linq;
using Kea;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Collections;

namespace KeaTest
{
    static class Kea
    {

        public static void Test()
        {
            //var po = Product
            //            .ReadBySupplierId()
            //            .Where(p => p.ProductName.Length < 14)
            //            .Join(OrderDetail.ReadByProductId(), p => p.ProductId);

            //using (var ep = po.GetEnumerableProvider((p, od) => new { p.ProductId, p.ProductName, od.OrderId }))
            //{
            //    foreach (int pid in System.Linq.Enumerable.Range(1, 10))
            //    {
            //        Console.WriteLine(pid);
            //        foreach (var p in ep.GetEnumerable(pid))
            //        {
            //            Console.WriteLine(p);
            //        }


            //    }
            //}



            var pr1 = Product
                        .ReadBySupplierId()
                        .Where(p => p.ProductName.Length < 14);

            using (var ep = pr1.GetEnumerableProvider(p => new { p.ProductId, p.ProductName }))
            {
                foreach (int pid in System.Linq.Enumerable.Range(1, 10))
                {
                    Console.WriteLine(pid);
                    foreach (var p in ep.GetEnumerable(pid))
                    {
                        Console.WriteLine(p);
                    }


                }
            }




            #region Query Data

            var products22 = Product.ReadByProductName().IndexBetween("P", "Q");                                      //Define
            var products = Product.Read().Where(p => p.ProductName.StartsWith("P"));                                      //Define
            Output("Product Count =", products.Count());                        //Execute


            var products2 = products.Where(p => (p.UnitPrice ?? 0M) < 10.0M);   //Define
            Output("Product Count less than $10 =", products2.Count());         //Execute


            var productList = products2.Select(p => p.ProductName);             //Define
            Output("Products less than $10 =", productList);                    //Execute
            productList.ToArray();                                              //Execute


            var productList2 = products2                                        //Define
                                .OrderBy(p => p.UnitPrice)
                                .ThenBy(p => p.ProductId)
                                .Select(p => p.ProductName);
            Output("Products less than $10 Ordered =", productList2);           //Execute


            var productList3 = products2                                        //Define
                                .Lookup(Category.Lookup(c => c.Description), p => p.CategoryId)
                                .Lookup(Supplier.Lookup(s => s.CompanyName + " (" + s.Country + ")"), (p, c) => p.SupplierId)
                                .OrderBy((p, c, s) => c)
                                .ThenBy((p, c, s) => s)
                                .Select((p, c, s) => new { Category = c, p.ProductName, Supplier = s });
            Output("Products3 less than $10 Ordered =", productList3);           //Execute


            var employeeOrderCounts = Order                                     //Define
                                        .Read()
                                        .Select(o => o.EmployeeId)
                                        .Where(eid => eid.HasValue)
                                        .GroupBy(eid => eid.Value)
                                        .Lookup(Employee.Lookup(e => e.Description), eg => eg.Key)
                                        .Select((eg, e) => new { Employee = e, OrdersTaken = eg.Count() })
                                        .OrderByDescending(a => a.OrdersTaken);
            Output("Employee Order Count =", employeeOrderCounts);              //Execute


            var employee9Details = Employee                                     //Define
                                    .ReadByEmployeesId(9)
                                    .Select(e => new { e.FirstName, e.LastName, e.BirthDate, e.Description })
                                    .First();                                   //Execute
            Output("Employee 9", employee9Details);


            var employees678 = new[] { 6, 7, 8 }                                //Define
                                .Lookup(Employee.Lookup(e => new { e.FirstName, e.LastName, e.BirthDate, e.Description }))
                                .Select((eid, ed) => ed);
            Output("Employees 6, 7 & 8", employees678);                         //Execute


            var productNames = new[] { "Côte de Blaye", "Tofu", "Fløtemysost", "Thüringer Rostbratwurst", "NuNuCa Nuß-Nougat-Creme" };
            var selectedProducts = productNames
                                    .Join(Product.ReadByProductName())
                                    .OrderBy((pn, p) => p.UnitsInStock)
                                    .ThenByDescending((pn, p) => p.UnitsOnOrder)
                                    .Select((pn, p) => new { pn, p.ProductId, p.UnitPrice });
            Output("Selected Products", selectedProducts);


            var orderDetailsByProductWithQuantityGT100 = OrderDetail
                                                .ReadByProductId()
                                                .Where(od => od.Quantity > 100);

            Output("OrderDetails By Product With Quantity Greater Then 100", orderDetailsByProductWithQuantityGT100.Select(od => od));


            var cheepProductsThatHaveNeverHadABulkOrder = Product
                                                            .Read()
                                                            .Where(p => p.UnitPrice < 10.0m)
                                                            .Select(c => c)
                                                            .Except(orderDetailsByProductWithQuantityGT100, p => p.ProductId)
                                                            .Select(p => new { p.ProductId, p.ProductName, p.UnitPrice });

            Output("Cheep Products That Have Never Had A BulkOrder", cheepProductsThatHaveNeverHadABulkOrder);


            #endregion


            #region Manipulate Data


            Customer.CreateOne(c =>
            {
                c.CustomerId = "NEWON";
                c.CompanyName = "New One";
                c.ContactTitle = "Ms";
                c.ContactName = "Smith";
                c.Address = "123 Weta Street";
                c.Country = "New Zealand New Zealand New Zealand ";
                c.City = "Duniden";
                c.Phone = "064 3 876 345";
            });

            var newCustomer = Customer.ReadByCustomerId("NEWON");
            var newCustomerDescription = newCustomer.Select(c => new { c.CompanyName, c.ContactName, c.Address, c.Country, c.City });

            Output("New Customer", newCustomerDescription);

            newCustomer.WriteOne(c =>
            {
                c.Country = "Aotearoa";
                c.City = "Dunedin";
            });

            Output("New Customer after update", newCustomerDescription);


            newCustomer.Delete();

            #endregion


            #region Old to replace



            var allCustomersAndTheirOrdersAndDetails = Customer
                                .Read()
                                .Join(Order.ReadByCustomerId(), c => c.CustomerId)
                                .Join(OrderDetail.Read(), (c, o) => o.OrderId)
                                .OrderByDescending((c, o, od) => c.CompanyName)
                                .ThenBy((c, o, od) => od.UnitPrice)
                                .Select((c, o, od) => new { c.CompanyName, o.OrderDate, od.Quantity, od.UnitPrice })
                                .ToArray();


            //Update One customer
            Customer.ReadByCustomerId("AROUT").WriteOne(c => c.CompanyName = "Around the corner");

            //Update them back again. A different way this time.
            using (var c = Customer.ReadByCustomerId("AROUT").WriteOne())
            {
                c.Item.CompanyName = "Around the Horn";
                c.Save();
            }

            //Reads some properties off one customer
            string companyName;
            string customerId;
            using (var c = Customer.ReadByCustomerId("AROUT").ReadOne())
            {
                companyName = c.Item.CompanyName;
                customerId = c.Item.CustomerId;
            }


            var customersBetweenBandDViaIndex = Customer
                                                    .Read()
                                                    .IndexBetween("B", "E")
                                                    .Select(q => new { q.CustomerId, q.CompanyName })
                                                    .ToArray();

            var customersBetweenBandDViaFilter = Customer
                                                    .Read()
                                                    .Where(c => c.CustomerId.CompareTo("B") >= 0)
                                                    .Where(c => c.CustomerId.CompareTo("E") <= 0)
                                                    .Select(q => new { q.CustomerId, q.CompanyName })
                                                    .ToArray();

            var allOrderDetails = OrderDetail.Read().Select(q => new { q.OrderId, q.ProductId, q.Quantity, q.UnitPrice }).ToArray();



            var customersAndTheirOrdersAndDetailsBetweenBandD = Customer
                            .Read()
                            .IndexBetween("B", "E")
                            .Join(Order.ReadByCustomerId(), c => c.CustomerId)
                            .Join(OrderDetail.Read(), (c, o) => o.OrderId)
                            .Lookup(Product.Lookup(p => p.ProductName, "UNKNOWN"), (c, o, od) => od.ProductId)
                            .Select((c, o, od, p) => new { c.CustomerId, c.CompanyName, o.OrderId, o.OrderDate, od.ProductId, od.Quantity, od.UnitPrice, p })
                            .ToArray();

            var ordersGreaterThen10500AndTheirDetails = OrderDetail
                            .Read()
                            .IndexGTE(10500)
                            .Lookup(Order.Lookup(), od => od.OrderId)
                            .Lookup(Customer.Lookup(), (od, o) => o.CustomerId)
                            .Select((od, o, c) => new { c.CustomerId, c.CompanyName, o.OrderId, o.OrderDate, od.ProductId, od.Quantity, od.UnitPrice })
                            .ToArray();

            Output(ordersGreaterThen10500AndTheirDetails);

            var allEmployees = Employee.Read().Select(e => new { e.FirstName, e.LastName }).ToArray();
            var allCustomers = Customer.Read().Select(c => new { c.CustomerId, c.CompanyName }).ToArray();
            var allOrders = Order.Read().Select(o => new { o.CustomerId, o.OrderId }).ToArray();
            var allProducts = Product.Read().Select(p => p.ProductName.ToString()).ToArray();

            Output(allProducts);



            Output(Customer.Read().Select(c => c.City).GroupBy(c => c).Select(c => new { City = c.Key, Count = c.Count() }).OrderByDescending(c => c.Count));

            Output(Customer.ReadByCity("São Paulo").Select(c => new { c.CompanyName, c.ContactName, c.Address }));
            Output(Customer.ReadByCity("São Paulo").Count());


            Console.ReadKey();

            #endregion
        }


        #region Output
        private static void Output(string text, object value)
        {
            Console.WriteLine(text);

            IEnumerable en = value as IEnumerable;
            if (en == null)
            {
                Output(value);
            }
            else
            {
                var e = en.GetEnumerator();
                using (e as IDisposable)
                {
                    if (!e.MoveNext())
                    {
                        Console.WriteLine("EMPTY");
                    }
                    else
                    {
                        object v = e.Current;
                        if (!v.GetType().IsClass || v is string)
                        {
                            while (true)
                            {
                                Console.WriteLine(e.Current);
                                if (!e.MoveNext()) break;
                            }
                        }
                        else
                        {
                            var ps = v.GetType().GetProperties();
                            foreach (PropertyInfo p in ps) Console.Write(":" + p.Name);
                            while (true)
                            {
                                Console.WriteLine();
                                foreach (PropertyInfo p in ps)
                                {
                                    object pv = p.GetValue(e.Current, null);
                                    if (pv == null) Console.Write("NULL");
                                    else Console.Write(pv);
                                    Console.Write(':');
                                }
                                if (!e.MoveNext()) break;
                            }
                        }
                    }
                }
            }
            Console.WriteLine();
            Console.WriteLine("----------------------------------------------");
        }


        private static void Output(IEnumerable list)
        {
            foreach (object t in list) Output(t);
        }
        private static void Output(object data)
        {
            if (data == null) Console.WriteLine("Null");

            //            Console.Write(data.GetType().Name);
            Console.Write(": ");

            if (data is string || data is int || data is DateTime || data is bool || data is short)
            {
                Console.Write(data);
                Console.Write(";  ");
            }
            else
            {
                foreach (PropertyInfo p in data.GetType().GetProperties())
                {
                    if (!p.CanRead) continue;

                    Console.Write(p.GetValue(data, null));
                    Console.Write(";  ");
                }
            }
            Console.WriteLine();
        }
        #endregion
    }


}
